package com.hcinfos.gsoo.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.hcinfos.gsoo.config.Constants;
import com.hcinfos.gsoo.enity.Area;
import com.hcinfos.gsoo.enity.City;
import com.hcinfos.gsoo.util.CommonUtil;

public class DBUtil {

	private Context context;
	private DBHelper dbHelper;
	private ContentValues values;
	private SQLiteDatabase db;

	private final String city_table = "city";
	private final String index = "aphaIndex";
	private final String cityName = "cityName";
	private final String cityId = "cityId";

	private final String imageCache_table = "imageCache";
	private final String time_stamp = "time_stamp";
	private final String past_time = "past_time";
	private final String url = "url";

	public DBUtil(Context context) {

		this.context = context;
		dbHelper = new DBHelper(context);
		db = dbHelper.getWritableDatabase();
	}

	private static DBUtil dbUtil;

	public static DBUtil newInstance(Context context) {
		if (null == dbUtil) {
			dbUtil = new DBUtil(context);
		}
		return dbUtil;
	}

	/**
	 * 添加城市
	 * 
	 * @param cityList
	 */
	public void insertCityList(List<City> cityList) {
		if (cityList.size() == 0)
			return;
		db.delete(city_table, null, null);
		db.beginTransaction();
		for (City city : cityList) {
			values = new ContentValues();
			values.put(cityName, city.getName());
			values.put(index, city.getIndex());
			values.put(cityId, city.getCityId());
			db.insert(city_table, null, values);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	/**
	 * 根据关键字来查找
	 * 
	 * @param keyWord
	 * @return
	 */
	public Cursor selectNameByKeyWord(String keyWord) {
		// db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from city where cityName like ? Limit 5 Offset 0",
				// "select * from lines Limit 3 Offset 0", null
				new String[] { "%" + keyWord + "%" });
		Log.i("wxf", cursor.getCount() + "");
		// if (db.isOpen()) {
		// db.close();
		// }
		return cursor;
	}

	public List<City> queryCityByKeyWord(String keyword) {

		List<City> list = new ArrayList<City>();
		Cursor cursor = db.rawQuery("select *  from " + city_table
				+ " where cityName like ? ",
				new String[] { "%" + keyword + "%" });
		if (cursor != null) {
			while (cursor.moveToNext()) {
				City city = new City();
				city.setName(cursor.getString(cursor.getColumnIndex(cityName)));
				city.setIndex(cursor.getString(cursor.getColumnIndex(index)));
				city.setCityId(cursor.getString(cursor.getColumnIndex(cityId)));
				list.add(city);
			}
		}
		return list;
	}

	/**
	 * 查询不重复城市字母
	 */
	public List<String> queryCityIndex() {

		List<String> indexList = new ArrayList<String>();
		Cursor cursor = db.rawQuery("select aphaIndex from " + city_table
				+ " group by aphaIndex order by aphaIndex asc", null);
		if (cursor != null) {
			while (cursor.moveToNext()) {
				indexList.add(cursor.getString(cursor
						.getColumnIndex("aphaIndex")));
			}
		}
		return indexList;
	}

	/**
	 * 根据城市名字查询城市信息
	 */
	public City queryCityByCityName(String name) {
		Cursor cursor = db.rawQuery("select * from " + city_table
				+ " where cityName = ?", new String[] { name });
		City city = null;
		if (cursor != null) {
			while (cursor.moveToNext()) {
				city = new City();
				city.setName(cursor.getString(cursor.getColumnIndex(cityName)));
				city.setIndex(cursor.getString(cursor.getColumnIndex(index)));
				city.setCityId(cursor.getString(cursor.getColumnIndex(cityId)));
			}

		}
		return city;
	}

	/**
	 * 根据索引查询城市
	 */
	public List<City> queryCityListByIndex(String indexStr) {

		List<City> list = new ArrayList<City>();
		Cursor cursor = db.rawQuery("select * from " + city_table
				+ " where aphaIndex = ? order by aphaIndex asc",
				new String[] { indexStr });
		if (cursor != null) {
			while (cursor.moveToNext()) {
				City city = new City();
				city.setName(cursor.getString(cursor.getColumnIndex(cityName)));
				city.setIndex(cursor.getString(cursor.getColumnIndex(index)));
				city.setCityId(cursor.getString(cursor.getColumnIndex(cityId)));
				list.add(city);
			}
		}
		return list;
	}

	/**
	 * 图片缓存数据库相关
	 */

	public Cursor queryFromDbByImgUrl(String imageUrl) {
		db = dbHelper.getReadableDatabase();
		return db.rawQuery("select * from " + imageCache_table + "  where "
				+ url + "='" + imageUrl + "'", null);
	}

	public void update(String imageUrl) {
		db = dbHelper.getReadableDatabase();
		String sql = "update " + imageCache_table + " set " + time_stamp + "='"
				+ (new Date().getTime()) + "' where " + url + "='" + imageUrl
				+ "'";
		db.execSQL(sql);
	}

	public void insert(String imageUrl) {
		db = dbHelper.getReadableDatabase();
		String sql = "insert into " + imageCache_table + "(" + url + ","
				+ time_stamp + "," + past_time + ") values('" + imageUrl + "',"
				+ (new Date().getTime()) + "," + Constants.DayCount + ")";
		db.execSQL(sql);
	}

}
